﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetUserMenusForUser')
	BEGIN
		DROP  Procedure  [esf_sso].P_GetUserMenusForUser
	END

GO

-- [esf_sso].P_GetUserMenusForUser NULL, 'ESF.SSO.Client'
-- [esf_sso].P_GetUserMenusForUser 1, 'ESF.SSO.Client'
-- [esf_sso].P_GetUserMenusForUser 1, 'ESF.SSO.Admin'


CREATE Procedure [esf_sso].P_GetUserMenusForUser
	@pPersonID			BIGINT = NULL
	,@pApplicationCode	VARCHAR (60)
AS

	-- Usuario Publico
	IF @pPersonID IS NULL
	BEGIN
	
		SELECT
			DISTINCT VU.ChiBR_ID	ID
			,VU.ParBR_ID			ParentID
			,VU.ChiBR_Code			Code
			,VU.ParBR_Code			ParentCode
			,VU.ChiBR_Name			Name
			,VU.ChiBR_Description	Description
			,VU.URL
			,VU.ChiBR_Extra_I		Extra_I
		FROM 
			[esf_sso].VUserMenus VU
		-- UserMenu , UserGroup (parent)
		INNER JOIN [esf_sso].TreeBasicReference UG_UM ON
			UG_UM.BasicReferenceID = VU.ChiBR_ID
		WHERE 
			UG_UM.ParentBasicReferenceID = (SELECT ID FROM [esf_sso].VUserGroup WHERE Code = 'Public')
			AND VU.ChiBR_IsEnabled = 1
			AND VU.Application_IsEnabled = 1
			AND VU.Application_Code = @pApplicationCode
	
	END
	ELSE
	BEGIN
	
		SELECT
			DISTINCT VU.ChiBR_ID	ID
			,VU.ParBR_ID			ParentID
			,VU.ChiBR_Code			Code
			,VU.ParBR_Code			ParentCode
			,VU.ChiBR_Name			Name
			,VU.ChiBR_Description	Description
			,VU.URL
			,VU.ChiBR_Extra_I		Extra_I
		FROM 
			[esf_sso].VUserMenus VU
		-- UserMenu , UserGroup (parent)
		INNER JOIN [esf_sso].TreeBasicReference UG_UM ON
			UG_UM.BasicReferenceID = VU.ChiBR_ID
		INNER JOIN
			esf_sso.VUserGroup VUG ON VUG.ID = UG_UM.ParentBasicReferenceID
		INNER JOIN 
			[esf_sso].[User_BasicReference] UG ON UG.BasicReferenceID = VUG.ID
		WHERE 
			VU.ChiBR_IsEnabled = 1
			AND VU.Application_IsEnabled = 1
			AND UG.UserPersonID = @pPersonID
			AND VU.Application_Code = @pApplicationCode
		ORDER BY
			VU.ChiBR_Extra_I
			,VU.ChiBR_Name
			
	END
	
GO 